﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ManageVolunteer_SearchRegionVolunteerControl

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal Region As String, ByVal Region2 As String, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal VOrganizationID As String, ByVal noOrgVolunteer As Boolean, ByVal OrganizationID As Integer) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT distinct A.VolunteerID,A.VolunteerName,CASE WHEN vom2.VolunteerID is null THEN '1' ELSE '0' END as IsEnable ")
        sql.AppendFormat(", A.MobilePhone, A.Active ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        ' sql.AppendFormat("LEFT join VOrganizationMapping CD on A.VolunteerId=CD.VolunteerID and CD.VOrganizationID='{0}' ", VOrganizationID)
        sql.AppendFormat("LEFT join VOrganizationMapping vom2 on A.VolunteerId=vom2.VolunteerID and vom2.VOrganizationID<>0 ")
        sql.AppendFormat("WHERE A.Active IN ('2','Y') ")
        ' sql.AppendFormat("AND A.VolunteerID NOT IN (SELECT VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0}) ", OrganizationID)
        sql.AppendFormat("AND (A.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        sql.AppendFormat("AND (A.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        sql.AppendFormat("AND (A.VolunteerId = '{0}' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (A.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)
        'sql.AppendFormat("AND (A.VolunteerName not like '%(儲)%' ) ", VolunteerName) '限制需領有紀錄冊的志工才能加入志工隊 Add By Shihyu 2010/10/12
        sql.AppendFormat("AND (((A.MobilePhone like '%{0}%' or '{0}'='') or (A.OfficePhone like '%{0}%' or '{0}'=''))) ", TEL)

        If noOrgVolunteer = True Then
            sql.AppendFormat("and vom2.VOrganizationID is null ")
        End If

        sql.AppendFormat("ORDER BY IsEnable desc,A.VolunteerID asc")
        'MyObj.WriteLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

End Class
